package io.yizoo.system.util;

import io.elm.core.saas.SpringDataSourceConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
public class JDBCUtils {

    // 数据库连接池配置（可选）
    private static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    private static String URL;
    private static String USER;
    private static String PASSWORD;
    private static Connection connection;

    public JDBCUtils(SpringDataSourceConfig springDataSourceConfig) {
        URL = springDataSourceConfig.getUrl();
        USER = springDataSourceConfig.getUsername();
        PASSWORD = springDataSourceConfig.getPassword();
        try {
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("数据库驱动初始化失败",e);
        }
    }


    /**
     * 获取数据库连接（推荐使用连接池实现）
     * @return Connection 对象
     * @throws SQLException 如果获取连接失败
     */
    public  Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        }
        return connection;
    }

    /**
     * 执行 DML 操作（INSERT/UPDATE/DELETE）
     * @param sql SQL 语句
     * @param params 参数数组
     * @return 受影响的行数
     */
    public  int executeUpdate(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            setParameters(pstmt, params);
            System.out.println("SQL: " + pstmt.toString());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("DML 操作执行失败", e);
        } finally {
            closeResources(conn, pstmt, null);
        }
    }

    /**
     * 执行查询操作
     * @param sql SQL 语句
     * @param params 参数数组
     * @return ResultSet 结果集
     */
    public  ResultSet executeQuery(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            setParameters(pstmt, params);
            System.out.println("SQL: " + pstmt.toString());
            return pstmt.executeQuery();
        } catch (SQLException e) {
            throw new RuntimeException("查询操作执行失败", e);
        }
    }

    /**
     * 设置 PreparedStatement 参数
     * @param pstmt PreparedStatement 对象
     * @param params 参数数组
     * @throws SQLException 如果参数设置失败
     */
    private  void setParameters(PreparedStatement pstmt, Object... params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
        }
    }

    /**
     * 关闭数据库资源（使用 try-with-resources 更佳）
     * @param conn Connection 对象
     * @param stmt Statement 对象
     * @param rs ResultSet 对象
     */
    public  void closeResources(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
        } catch (SQLException e) {
            // 忽略关闭异常
        }
        try {
            if (stmt != null) stmt.close();
        } catch (SQLException e) {
            // 忽略关闭异常
        }
        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            // 忽略关闭异常
        }
    }

    /**
     * 开始事务
     */
    public  void beginTransaction() {
        try {
            getConnection().setAutoCommit(false);
        } catch (SQLException e) {
            throw new RuntimeException("事务开始失败", e);
        }
    }

    /**
     * 提交事务
     */
    public  void commitTransaction() {
        try {
            getConnection().commit();
            getConnection().setAutoCommit(true);
        } catch (SQLException e) {
            throw new RuntimeException("事务提交失败", e);
        }
    }

    /**
     * 回滚事务
     */
    public  void rollbackTransaction() {
        try {
            getConnection().rollback();
            getConnection().setAutoCommit(true);
        } catch (SQLException e) {
            throw new RuntimeException("事务回滚失败", e);
        }
    }

    /**
     * 示例：使用工具类执行查询
     */
    public static void main(String[] args) {
     //   try {
            // 开始事务
//            beginTransaction();
//
//            // 执行更新操作
//            executeUpdate("INSERT INTO users (name, email) VALUES (?, ?)",
//                    "John Doe", "john@example.com");
//
//            // 执行查询操作
//            try (ResultSet rs = executeQuery("SELECT * FROM users WHERE name = ?", "John Doe")) {
//                while (rs.next()) {
//                    System.out.println("User ID: " + rs.getInt("id"));
//                    System.out.println("Name: " + rs.getString("name"));
//                    System.out.println("Email: " + rs.getString("email"));
//                }
//            }
//
//            // 提交事务
//            commitTransaction();
//        } catch (Exception e) {
//            rollbackTransaction();
//            e.printStackTrace();
//        }
 //   }
}
}
